'''
# Coding:utf-8
# Project: iiop
# Author: rtf
# Time: 2023-03-24 11:01:32
# FileName: dynamic_excel.py
# Software: PyCharm
'''

import openpyxl


sheet0_value = {
    "cd_total_table": [
        {"sac_workload": "D3", "sac_labor_rate": "E3", "sac_cost": "F3"},
        {"sac_workload": "D33", "sac_labor_rate": "E33", "sac_cost": "F33"},
        {"sac_workload": "D34", "sac_labor_rate": "E34", "sac_cost": "F34"},
        {"sac_workload": "D35", "sac_labor_rate": "E35", "sac_cost": "F35"}
    ],
    "duddi_workload": "D13", "duddi_labor_rate": "E13", "duddi_cost": "F13",
    "cd_total_table1": [
        {"a": "D14", "b": "E14", "c": "F14"},
        {"a": "D14", "b": "E14", "c": "F14"},
        {"a": "D14", "b": "E14", "c": "F14"},
        {"a": "D14", "b": "E14", "c": "F14"}
    ],
    "other_cost": "F15", "total": "F16"
}

sheet0_key = {
    "sac_workload": "D3", "sac_labor_rate": "E3", "sac_cost": "F3",
    "duddi_workload": "D13", "duddi_labor_rate": "E13", "duddi_cost": "F13",
    "a": "D14", "b": "E14", "c": "F14",
    "other_cost": "F15", "total": "F16"
}

sheet1_value = {
    "sac_workload": 12, "sac_labor_rate": 12, "sac_cost": 14,
    "hpc_workload": 23, "hpc_labor_rate": 24, "hpc_cost": 25,
    "cdf_workload": 0, "cdf_labor_rate": 0, "cdf_cost": 0,
    "sfd_workload": 435, "sfd_labor_rate": 0.21, "sfd_cost": 91.35,
    "sid_workload": 52,  "sid_labor_rate": 0.21, "sid_cost": 10.92,
    "si_workload": 104, "si_labor_rate": 0.15, "si_cost": 15.6,
    "sii_workload": 72, "sii_labor_rate": 0.15, "sii_cost": 10.8,
    "boe_workload": 0, "boe_labor_rate": 0, "boe_cost": 0,
    "dprd_workload": 0, "dprd_labor_rate": 0.21, "dprd_cost": 0,
    "dsiccq_workload": 0, "dsiccq_labor_rate": 0.18, "dsiccq_cost": 0,
    "duddi_workload": 0, "duddi_labor_rate": 0.15, "duddi_cost": 0,
    "other_cost": 0, "total": 128.67
}

sheet1_key = {
    "sac_workload": "D3", "sac_labor_rate": "E3", "sac_cost": "F3",
    "hpc_workload": "D4", "hpc_labor_rate": "E4", "hpc_cost": "F4",
    "cdf_workload": "D5", "cdf_labor_rate": "E5", "cdf_cost": "F5",
    "sfd_workload": "D6", "sfd_labor_rate": "E6", "sfd_cost": "F6",
    "sid_workload": "D7",  "sid_labor_rate": "E7", "sid_cost": "F7",
    "si_workload": "D8", "si_labor_rate": "E8", "si_cost": "F8",
    "sii_workload": "D9", "sii_labor_rate": "E9", "sii_cost": "F9",
    "boe_workload": "D10", "boe_labor_rate": "E10", "boe_cost": "F10",
    "dprd_workload": "D11", "dprd_labor_rate": "E11", "dprd_cost": "F11",
    "dsiccq_workload": "D12", "dsiccq_labor_rate": "E12", "dsiccq_cost": "F12",
    "duddi_workload": "D13", "duddi_labor_rate": "E13", "duddi_cost": "F13",
    "other_cost": "F14", "total": "F15"
}

# 根据偏移量计算单元格位置
def cell_offset(cell, offset):
    col = ""
    row = ""
    for x in cell:
        if x.isdigit():
            row = f"{row}{x}"
        else:
            col = f"{col}{x}"
    row = int(row) + offset
    offset_cell = f"{col}{row}"
    return offset_cell


def dynamic_process(path, sheet_key, sheet_value):
    wb = openpyxl.load_workbook(path)  # 返回一个workbook数据类型的值
    offset = 0
    for i, value in enumerate(sheet_value):
        ws = wb.worksheets[i]
        for k, v in value.items(): # 获取数据中的key，value
            if isinstance(v, list):
                for index, v_value in enumerate(v):
                    for k1, v1 in v_value.items():
                        offset_cell = cell_offset(sheet_key[i].get(k1), offset)
                        ws[offset_cell] = v1
                    # 因模板中有一行，所以偏移量少加一行
                    if index < len(v) - 1:
                        offset += 1
            else:
                offset_cell = cell_offset(sheet_key[i].get(k), offset)
                ws[offset_cell] = v

    wb.save(path)


sheet_values = [sheet0_value, sheet1_value]
sheet_keys = [sheet0_key, sheet1_key]
file_path = "/home/rtf/Project/Jna/iiop/download/test.xlsx"

dynamic_process(file_path, sheet_keys, sheet_values)


